﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using System.Data;
using System.Data.SqlClient;

namespace CMS
{
    public class SqlHelper
    {

        private string strCn = "server=pc.hstc.cn;database=dhd2014112128;uid=2014112128;pwd=19940601";

        SqlConnection conn = null;

        public SqlDataReader dr = null;

        public SqlCommand cmd = null;

        //构造方法,连接数据库
        public SqlHelper()
        {
            this.conn = new SqlConnection(this.strCn);
            this.conn.Open();
        }

        /// <summary>
        /// 通过sql语句,查询返回一个SqlDataReader数据类型的对象
        /// </summary>
        public SqlDataReader ExecuteReader(string query_sql, params SqlParameter[] pms)
        {
            using (cmd = new SqlCommand(query_sql, conn))
            {
                if (pms != null) cmd.Parameters.AddRange(pms);
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            return dr;
        }

        public int ExecuteNonQuery(string sql, params SqlParameter[] pms)
        {
            int i = 0;
            cmd = new SqlCommand(sql, conn);
            i = cmd.ExecuteNonQuery();
            return i;
        }

        //查询一行一列数据
        public object ExecuteScalar(string query_sql)
        {

            object ob = null;
            using (cmd = new SqlCommand(query_sql, conn))
            {
                ob = cmd.ExecuteScalar();
            }
            return ob;
        }

        //分页查询
        //sql1: select count(*) from 表名
        //sql2: select top 3 * from Stu_Score where id not in(select top 【当前页-1】*3 id from Stu_Score)
        public void fenye_query(string sql1, string sql2, string url,FenYe fenye,params SqlParameter[] pms)
        {
            
            cmd = new SqlCommand(sql1,conn);
            if (pms != null) cmd.Parameters.AddRange(pms);
            dr = cmd.ExecuteReader();
            if (dr.Read()) {
                fenye.rowCount = int.Parse(dr[0].ToString());
                fenye.pageCount = Math.Ceiling(fenye.rowCount/ fenye.pageSize);
                dr.Close();               //关闭
            }

            cmd.Parameters.Clear();     //清空Parameters集合。
           

            //cmd = new SqlCommand(sql2, conn);
            //if (pms != null) cmd.Parameters.AddRange(pms);
            //dr = cmd.ExecuteReader();          

            using (cmd = new SqlCommand(sql2, conn))
            {
                if (pms != null) cmd.Parameters.AddRange(pms);
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        
            fenye.dr = dr;
            //分页导航条
            string navigate = "";
            //上一页
            if (fenye.pageNow > 1)
            {
                string prePage = (fenye.pageNow - 1).ToString();
                navigate = "<a id='last' href='{1}?pageNow={0}'>上一页</a>&nbsp;&nbsp;";
                navigate = string.Format(navigate, prePage,url);
            }

            //按1-10、11-20、21-30这样显示
            /*
                 分析： Math.Floor((pageNow-1)/10)*10 + 1
             
            */
            int showCount = 10;      //显示10页
            double start = Math.Floor((fenye.pageNow - 1) / 10.0) * showCount + 1;
            double index = start;

            //向前显示十页
            if (start > 10)
            {
                string go = (start - 1).ToString();
                navigate = navigate + "<a href='{1}?pageNow={0}'>上十页</a>";
                navigate = string.Format(navigate, go,url);
            }

            //十页显示
            for (; start < index + showCount; start++)
            {
                if (start > fenye.pageCount)
                {
                    break;
                }
                string go = start.ToString();
                navigate = navigate + "<a href='{1}?pageNow={0}'>{0}</a>";
                navigate = string.Format(navigate, go,url);
            }

            //向后翻十页
            if (fenye.pageNow <= fenye.pageCount - 10)
            {
                navigate = navigate + "<a href='{1}?pageNow={0}'>下十页</a>";
                navigate = string.Format(navigate, start.ToString(),url);
            }


            //下一页
            if (fenye.pageNow < fenye.pageCount)
            {
                string nextPage = (fenye.pageNow + 1).ToString();
                navigate = navigate + "<a id='next' href='{1}?pageNow={0}'>下一页</a>&nbsp;&nbsp;";
                navigate = string.Format(navigate, nextPage,url);
            }

            fenye.navigate = navigate;
            
        }

        //关闭SqlDataReader
        public void close_dr() {
            this.dr.Close();
        }
        //关闭数据库
        public void close_Connection()
        {
            if (this.conn != null)
            {
                this.conn.Close();
            }
        }
    }
}